package com.example.keshe;

import com.example.keshe.Dao.impl.ClothingDaoImpl;
import com.example.keshe.util.DruidUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.ss.usermodel.DataFormatter;

/**
 * 用于导入和导出服装数据到Excel的实用工具类。
 */
public class ExcelClothing {

    /**
     * 从Excel文件导入服装数据。
     *
     * @param file 包含服装数据的Excel文件。
     * @return 从Excel文件导入的Clothing对象列表。
     */
    public static List<Clothing> importFromExcel(File file) {
        List<Clothing> clothingList = new ArrayList<>();
        DataFormatter dataFormatter = new DataFormatter();
        try (Connection connection = DruidUtil.getConnection();
             Workbook workbook = new XSSFWorkbook(new FileInputStream(file))) {
            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            // 遍历每一行
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                // 跳过表头行
                if (row.getRowNum() == 0) {
                    continue;
                }
                // 从单元格中获取数据
                String clothingId = dataFormatter.formatCellValue(row.getCell(0));
                String name = dataFormatter.formatCellValue(row.getCell(1));
                String brand = dataFormatter.formatCellValue(row.getCell(2));
                String size = dataFormatter.formatCellValue(row.getCell(3));
                String price = dataFormatter.formatCellValue(row.getCell(4));
                String quantity = dataFormatter.formatCellValue(row.getCell(5));
                String color = dataFormatter.formatCellValue(row.getCell(6));
                String season = dataFormatter.formatCellValue(row.getCell(7));
                String category = dataFormatter.formatCellValue(row.getCell(8));
                // 创建Clothing对象并添加到列表
                Clothing clothing = new Clothing(clothingId, name, brand, size, price, quantity, color, season, category);
                clothingList.add(clothing);
                // 将数据插入数据库
                ClothingDaoImpl clothingDao = new ClothingDaoImpl(connection);
                clothingDao.insert(clothing);
            }
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
        return clothingList;
    }

    /**
     * 将服装数据导出到Excel文件。
     *
     * @param clothingList 包含Clothing对象的列表，要导出的数据。
     * @param outputFile   输出的Excel文件。
     */
    public static void exportToExcel(List<Clothing> clothingList, File outputFile) {
        try (Workbook workbook = new XSSFWorkbook()) {
            // 创建工作表
            Sheet sheet = workbook.createSheet("服装数据");
            // 创建表头行
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("服装编号");
            headerRow.createCell(1).setCellValue("名称");
            headerRow.createCell(2).setCellValue("品牌");
            headerRow.createCell(3).setCellValue("大小");
            headerRow.createCell(4).setCellValue("价格");
            headerRow.createCell(5).setCellValue("数量");
            headerRow.createCell(6).setCellValue("颜色");
            headerRow.createCell(7).setCellValue("季节");
            headerRow.createCell(8).setCellValue("类别");
            // 填充数据行
            int rowNum = 1;
            for (Clothing clothing : clothingList) {
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(clothing.getClothingId());
                row.createCell(1).setCellValue(clothing.getName());
                row.createCell(2).setCellValue(clothing.getBrand());
                row.createCell(3).setCellValue(clothing.getSize());
                row.createCell(4).setCellValue(clothing.getPrice());
                row.createCell(5).setCellValue(clothing.getQuantity());
                // 新添加的三个字段
                row.createCell(6).setCellValue(clothing.getColor());
                row.createCell(7).setCellValue(clothing.getSeason());
                row.createCell(8).setCellValue(clothing.getCategory());
            }
            // 将工作簿写入输出文件
            try (FileOutputStream fileOut = new FileOutputStream(outputFile)) {
                workbook.write(fileOut);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
